package org.boot.fast.core.api.mapper.provider;

import cn.hutool.extra.spring.SpringUtil;
import com.google.gson.Gson;
import com.google.gson.JsonSyntaxException;
import lombok.extern.slf4j.Slf4j;
import org.boot.fast.core.api.mapper.provider.entity.AnalysisResultInfo;
import org.boot.fast.core.api.mapper.provider.impl.DynamicDeleteProvider;
import org.boot.fast.core.api.mapper.provider.impl.DynamicInsertProvider;
import org.boot.fast.core.api.mapper.provider.impl.DynamicQueryProvider;
import org.boot.fast.core.api.mapper.provider.impl.DynamicUpdateProvider;
import org.boot.fast.core.mate.DatabaseMetaService;
import org.boot.fast.core.mate.entity.ColumnInfo;
import org.boot.fast.core.mate.entity.TableStructure;
import org.boot.fast.dao.entity.FastApiConfigDO;
import org.boot.fast.util.CollUtil;
import org.boot.fast.util.GsonUtil;
import org.boot.fast.util.ObjectUtil;
import org.boot.fast.util.StrUtil;

import java.util.*;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
import java.util.stream.Collectors;

/**
 * SQL Provider类 - 动态SQL构建器
 */
@Slf4j
@SuppressWarnings("unchecked")
public class DynamicSqlProvider {

    //忽略参数
    public static List<String> SQL_KEYWORDS = Arrays.asList(
            "sortOrder",
            "sortBy",
            "pageSize",
            "pageNo"
    );
    private DatabaseMetaService metaService = SpringUtil.getBean(DatabaseMetaService.class);

    /**
     * 构建动态查询SQL；获取查询结果列表
     * @param params
     * @return
     */
    public String buildQuerySql(Map<String, Object> params) {
        try{
            FastApiConfigDO apiConfig = (FastApiConfigDO) params.get("apiConfig");
            TableStructure structure = metaService.getTableStructure(apiConfig.getTableName());
            if (ObjectUtil.isEmpty(structure.getTableInfo())) throw new RuntimeException("表" + apiConfig.getTableName() + "结构不存在");
            return DynamicQueryProvider.buildQuerySql(params);
        } catch (Exception e) {
            throw new RuntimeException(e);
        }

    }


    /**
     * 构建动态查询SQL；获取查询结果总数
     * @param params
     * @return
     */
    public String buildCountSql(Map<String, Object> params) {
        try{
            FastApiConfigDO apiConfig = (FastApiConfigDO) params.get("apiConfig");
            TableStructure structure = metaService.getTableStructure(apiConfig.getTableName());
            if (ObjectUtil.isEmpty(structure.getTableInfo())) throw new RuntimeException("表" + apiConfig.getTableName() + "结构不存在");
            return DynamicQueryProvider.buildCountSql(params);
        } catch (Exception e) {
            throw new RuntimeException(e);
        }
    }

    /**
     * 构建动态更新SQL；增删改操作
     * @param params
     * @return
     */
    public String buildUpdateSql(Map<String, Object> params) {
        try {
            FastApiConfigDO apiConfig = (FastApiConfigDO) params.get("apiConfig");
            TableStructure structure = metaService.getTableStructure(apiConfig.getTableName());
            if (ObjectUtil.isEmpty(structure.getTableInfo())) throw new RuntimeException("表" + apiConfig.getTableName() + "结构不存在");

            String originalSql = getStringParam(params, "originalSql");
            Map<String, Object> conditionParams = (Map<String, Object>) params.get("params");
            // 检查SQL是否包含所有参数
            validateSqlFieldsWithException(originalSql, conditionParams);

            conditionParams = removeNullItems(conditionParams);
            if (originalSql.startsWith("UPDATE")) {
                // 检查SQL是否缺失必填字段
                validateSqlFieldsMustException(structure.getColumns(), conditionParams);
                AnalysisResultInfo analysisResultInfo = DynamicUpdateProvider.buildUpdateStatement((originalSql).trim(), conditionParams);
                ((Map<String, Object>) params.get("params")).putAll(analysisResultInfo.getParam());
                return analysisResultInfo.getSql();
            } else if (originalSql.startsWith("INSERT")) {
                AnalysisResultInfo analysisResultInfo = DynamicInsertProvider.buildInsertStatement((originalSql).trim(), conditionParams);
                ((Map<String, Object>) params.get("params")).putAll(analysisResultInfo.getParam());
                return analysisResultInfo.getSql();
            } else if (originalSql.startsWith("DELETE")) {
                AnalysisResultInfo analysisResultInfo = DynamicDeleteProvider.buildDeleteStatement((originalSql).trim(), conditionParams);
                ((Map<String, Object>) params.get("params")).putAll(analysisResultInfo.getParam());
                return analysisResultInfo.getSql();
            } else {
                return originalSql;
            }
        } catch (Exception e) {
            throw new RuntimeException("构建更新SQL失败: " + e.getMessage(), e);
        }
    }

    /**
     * 构建动态批量新增SQL
     * @param params
     * @return
     */
    public String buildInsertBatchSql(Map<String, Object> params) {
        try {
            FastApiConfigDO apiConfig = (FastApiConfigDO) params.get("apiConfig");
            TableStructure structure = metaService.getTableStructure(apiConfig.getTableName());
            if (ObjectUtil.isEmpty(structure.getTableInfo())) throw new RuntimeException("表" + apiConfig.getTableName() + "结构不存在");
            String originalSql = getStringParam(params, "originalSql");
            List<Map<String, Object>> conditionParams = (List<Map<String, Object>>) params.get("params");
            // 检查SQL是否包含所有参数
            validateSqlFieldsWithBatchException(originalSql, conditionParams);
            conditionParams = removeNullItems(conditionParams);
            if (originalSql.startsWith("INSERT")) {
                AnalysisResultInfo analysisResultInfo = DynamicInsertProvider.buildInsertBatchStatement((originalSql).trim(), conditionParams);
                params.put("params", analysisResultInfo.getParam());
                return analysisResultInfo.getSql();
            } else {
                return originalSql;
            }
        } catch (Exception e) {
            throw new RuntimeException("构建更新SQL失败: " + e.getMessage(), e);
        }
    }




    //~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~//
    public static List<Map<String, Object>> removeNullItems(List<Map<String, Object>> paramMap) {
        if (CollUtil.isEmpty(paramMap)) {
            return Collections.emptyList();
        }
        List<Map<String, Object>> resultList = new ArrayList<>();
        for (Map<String, Object> map : paramMap) {
            resultList.add(removeNullItems(map));
        }
        return resultList;
    }
    public static Map<String, Object> removeNullItems(Map<String, Object> paramMap) {
        if (paramMap == null || paramMap.isEmpty()) {
            return Collections.emptyMap();
        }
        Map<String, Object> resultMap = new HashMap<>();
        for (Map.Entry<String, Object> entry : paramMap.entrySet()) {
            if (ObjectUtil.isNotEmpty(entry.getValue()) && StrUtil.isNotEmpty(entry.getValue().toString())){
                resultMap.put(entry.getKey(), entry.getValue());
            }
        }
        return resultMap;
    }


    //检查是否缺失表中的必填字段
    public static void validateSqlFieldsMustException(List<ColumnInfo> columns, Map<String, Object> paramMap) {
        Set<String> missingFields = findMustFields(columns, paramMap);
        if (!missingFields.isEmpty()) {
            throw new RuntimeException("缺少必填的参数字段: " + missingFields);
        }

    }
    public static Set<String> findMustFields(List<ColumnInfo> columns, Map<String, Object> paramMap) {
        Set<String> sqlFields = new HashSet<>();
        for (ColumnInfo column : columns) {
            if (column.getIsNullable().equals("NO") && !paramMap.containsKey(column.getColumnName()) && !column.getColumnName().equals("id")) {
                sqlFields.add(column.getColumnName());
            }
        }
        return sqlFields;
    }

    // 检查是否缺失配置中的必要字段
    public static void validateSqlFieldsWithException(String sql, Map<String, Object> paramMap) {
        Set<String> missingFields = findMissingFields(sql, paramMap);
        if (!missingFields.isEmpty()) {
            throw new RuntimeException("缺少配置中必要的参数字段: " + missingFields);
        }
    }

    public static void validateSqlFieldsWithBatchException(String sql, List<Map<String, Object>> paramMap) {
        for (Map<String, Object> map : paramMap) {
            Set<String> missingFields = findMissingFields(sql, map);
            if (!missingFields.isEmpty()) {
                //移除相同的字段missingFields
                Set<String> removeFields = new HashSet<>();
                for (String field : missingFields) {
                    if (removeFields.contains(field)) {
                        missingFields.remove(field);
                    } else {
                        removeFields.add(field);
                    }
                }
                throw new RuntimeException("缺少配置中必要的参数字段: " + removeFields);
            }
        }

    }
    public static Set<String> findMissingFields(String sql, Map<String, Object> paramMap) {
        Set<String> sqlFields = new HashSet<>();
        Matcher matcher = Pattern.compile("#\\{([^}]+)\\}").matcher(sql);

        while (matcher.find()) {
            sqlFields.add(matcher.group(1).trim());
        }

        Set<String> missing = new HashSet<>();
        for (String field : sqlFields) {
            if (!paramMap.containsKey(field)) {
                missing.add(field);
            }
        }

        return missing;
    }

    /**
     * 转换参数格式：#{parameter} -> #{params.parameter}
     */
    public static String convertParameterFormat(String sql) {
        if (sql == null || !sql.contains("#{")) {
            return sql;
        }

        // 使用正则表达式匹配 #{...} 模式
        Pattern pattern = Pattern.compile("#\\{([^}]+)\\}");
        Matcher matcher = pattern.matcher(sql);

        StringBuffer result = new StringBuffer();
        while (matcher.find()) {
            String originalParam = matcher.group(1); // 获取参数名，如 "file_name"

            // 如果参数名已经包含 "params."，则跳过
            if (originalParam.startsWith("params.")) {
                matcher.appendReplacement(result, "#{" + originalParam + "}");
                continue;
            }

            // 检查是否是简单参数名（不包含点号或其他特殊字符）
            if (isSimpleParameterName(originalParam)) {
                // 转换为 #{params.parameter}
                String convertedParam = "params." + originalParam;
                matcher.appendReplacement(result, "#{" + convertedParam + "}");
            } else {
                // 保持原样
                matcher.appendReplacement(result, "#{" + originalParam + "}");
            }
        }
        matcher.appendTail(result);

        return result.toString();
    }
    /**
     * 检查是否是简单参数名（只包含字母、数字、下划线）
     */
    private static boolean isSimpleParameterName(String paramName) {
        return paramName.matches("^[a-zA-Z_][a-zA-Z0-9_]*$");
    }

    /**
     * 判断是否是条件参数（用于WHERE子句）
     */
    public static boolean isConditionParam(String key) {
        return key.endsWith("_like") || key.endsWith("_gt") || key.endsWith("_ge") ||
                key.endsWith("_lt") || key.endsWith("_le") || key.endsWith("_ne") ||
                key.endsWith("_in") || key.endsWith("_notin") || key.endsWith("_between");
    }

    /**
     * 安全获取字符串参数
     */
    private String getStringParam(Map<String, Object> params, String key) {
        Object value = params.get(key);
        if (value == null) {
            throw new IllegalArgumentException("参数 '" + key + "' 不能为null");
        }
        return value.toString();
    }


    /**
     * 手动构建WHERE条件 - 避免ew参数问题
     */
    public static String buildWhereClause(Map<String, Object> params) {
        if (params == null || params.isEmpty()) {
            return "";
        }
        boolean hasCondition = false;
        StringBuilder whereBuilder = new StringBuilder();

        for (Map.Entry<String, Object> entry : params.entrySet()) {
            String key = entry.getKey();
            Object value = entry.getValue();

            if (isSystemParam(key) || value == null) {
                continue;
            }

            if (!hasCondition) {
                whereBuilder.append(" WHERE 1=1");
                hasCondition = true;
            }

            // 使用Wrapper构建条件，但手动转换为SQL
            if (key.endsWith("@like")) {
                String fieldName = key.substring(0, key.length() - 5);
                whereBuilder.append(" AND ").append(fieldName).append(" LIKE '%").append(escapeSql(value.toString())).append("%'");
            } else if (key.endsWith("@gt")) {
                String fieldName = key.substring(0, key.length() - 3);
                whereBuilder.append(" AND ").append(fieldName).append(" > ").append(formatValue(value));
            } else if (key.endsWith("@ge")) {
                String fieldName = key.substring(0, key.length() - 3);
                whereBuilder.append(" AND ").append(fieldName).append(" >= ").append(formatValue(value));
            } else if (key.endsWith("@lt")) {
                String fieldName = key.substring(0, key.length() - 3);
                whereBuilder.append(" AND ").append(fieldName).append(" < ").append(formatValue(value));
            } else if (key.endsWith("@le")) {
                String fieldName = key.substring(0, key.length() - 3);
                whereBuilder.append(" AND ").append(fieldName).append(" <= ").append(formatValue(value));
            } else if (key.endsWith("@ne")) {
                String fieldName = key.substring(0, key.length() - 3);
                whereBuilder.append(" AND ").append(fieldName).append(" != ").append(formatValue(value));
            } else if (key.endsWith("@in")) {
                String fieldName = key.substring(0, key.length() - 3);
                whereBuilder.append(" AND ").append(fieldName).append(" IN (").append(buildInClause(value)).append(")");
            } else if (key.endsWith("@notin")) {
                String fieldName = key.substring(0, key.length() - 6);
                whereBuilder.append(" AND ").append(fieldName).append(" NOT IN (").append(buildInClause(value)).append(")");
            } else if (key.endsWith("@between")) {
                String fieldName = key.substring(0, key.length() - 8);
                whereBuilder.append(" AND ").append(fieldName).append(" BETWEEN ").append(buildBetweenClause(value));
            } else {
                whereBuilder.append(" AND ").append(key).append(" = ").append(formatValue(value));
            }
        }
        return whereBuilder.toString();
    }

    /**
     * 构建IN子句
     */
    public static String buildInClause(Object value) {
        value = GsonUtil.toList(value);
        if (value instanceof Collection) {
            Collection<?> collection = (Collection<?>) value;
            return collection.stream()
                    .map(DynamicSqlProvider::formatValue)
                    .collect(Collectors.joining(", "));
        } else if (value instanceof Object[]) {
            Object[] array = (Object[]) value;
            return Arrays.stream(array)
                    .map(DynamicSqlProvider::formatValue)
                    .collect(Collectors.joining(", "));
        } else {
            return formatValue(value);
        }
    }



    /**
     * 构建BETWEEN子句
     */
    public static String buildBetweenClause(Object value) {
        if (value instanceof Object[] && ((Object[]) value).length == 2) {
            Object[] range = (Object[]) value;
            return formatValue(range[0]) + " AND " + formatValue(range[1]);
        } else {
            throw new IllegalArgumentException("BETWEEN查询需要两个值的数组");
        }
    }

    /**
     * 格式化值 - 安全处理SQL注入
     */
    public static String formatValue(Object value) {
        if (value == null) {
            return "NULL";
        } else if (value instanceof String) {
            return "'" + escapeSql(value.toString()) + "'";
        } else if (value instanceof Number) {
            return value.toString();
        } else if (value instanceof Boolean) {
            return ((Boolean) value) ? "1" : "0";
        } else if (value instanceof java.util.Date) {
            return "'" + new java.text.SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(value) + "'";
        } else if (value instanceof java.sql.Date) {
            return "'" + new java.text.SimpleDateFormat("yyyy-MM-dd").format(value) + "'";
        } else {
            return "'" + escapeSql(value.toString()) + "'";
        }
    }

    /**
     * SQL转义 - 防止SQL注入
     */
    public static String escapeSql(String value) {
        if (value == null) return "";
        return value.replace("'", "''")
                .replace("\\", "\\\\")
                .replace("\"", "\\\"");
    }


    public static boolean isSystemParam(String key) {
        return SQL_KEYWORDS.contains(key);
    }
}